IF OBJECT_ID (N'ODS.DW_SP_LOG', N'U') IS NULL  
BEGIN
	CREATE TABLE ODS.DW_SP_LOG -- ODS.DW_SP_LOG
	(ID int identity(1,1) primary key
	,ProcedureName varchar(200)
	,StartTime datetime
	,EndTime datetime
	,Operation varchar(100)
	,SourceTableView varchar(2000)
	,SourceCount int
	,TargetTable varchar(100)
	,TargetOriginCount int
	,DeleteCount int
	,InsertCount int
	,UpdateCount int
	,TargetCount int
	,RunResult varchar(20)
	,ErrorNo int
	,ErrorSeverity int
	,ErrorState int
	,ErrorLine int
	,ErrorMessage nvarchar(1000)
	,Description nvarchar(1000)
	);
END;
GO
IF OBJECT_ID (N'ODS.DW_SP_INFO', N'U') IS NULL  -- 
BEGIN
	CREATE TABLE ODS.DW_SP_INFO ( -- ODS.DW_SP_INFO
	ObjectId int primary key IDENTITY, --pk
	ProcedureName varchar(200) , -- sp名称 同EXCE 
	SourceTableView varchar(1000) , -- 数据来源
	TargetTable varchar(1000) , -- 目标表，多个用 ','隔开
	Operation varchar(100) , -- Tranck /。。。
	IsDeleted bit default 0 , -- 1.删除 0.未删除
	Project varchar(100) , -- 创建人
	ETL_Time datetime default getdate()
	);
END;
GO

-- =============================================
-- Author:<Pumbaa.Peng>
-- Create date:<2021-10-14>
-- Description:<添加或修改表或字段的注释>
-- =============================================
CREATE OR ALTER PROCEDURE ODS.SP_AddOrUpdateComment (@schema VARCHAR(50), @tableName VARCHAR(100), @columnName VARCHAR(100), @comment NVARCHAR(1000)) AS 
BEGIN
	IF len(@columnName) > 0
	BEGIN
		BEGIN TRY
			EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = @comment, @level0type = N'SCHEMA', @level0name = @schema, @level1type = N'TABLE', @level1name = @tableName, @level2type = N'COLUMN', @level2name = @columnName;
		END TRY

		BEGIN CATCH
			EXEC sys.sp_updateextendedproperty @name = N'MS_Description', @value = @comment, @level0type = N'SCHEMA', @level0name = @schema, @level1type = N'TABLE', @level1name = @tableName, @level2type = N'COLUMN', @level2name = @columnName;
		END CATCH
	END
	ELSE
	BEGIN
		BEGIN TRY
			EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = @comment, @level0type = N'SCHEMA', @level0name = @schema, @level1type = N'TABLE', @level1name = @tableName;
		END TRY

		BEGIN CATCH
			EXEC sys.sp_updateextendedproperty @name = N'MS_Description', @value = @comment, @level0type = N'SCHEMA', @level0name = @schema, @level1type = N'TABLE', @level1name = @tableName;
		END CATCH
	END
END
GO

-- =============================================
-- Author:<Pumbaa.Peng>
-- Create Date:<2021-10-14>
-- Description:<sp运行日志记录模板>
-- =============================================
CREATE OR ALTER PROCEDURE ODS.SP_DW_SP_LOG (@procedureName VARCHAR(100)) AS 
BEGIN
    BEGIN TRY
        --源表或视图
        DECLARE @sourceTableView VARCHAR(1000);
        --目标表
        DECLARE @targetTable VARCHAR(1000);
        --操作类型
        DECLARE @operation VARCHAR(100)
        --源表按照逻辑处理后应取得的记录条数
        DECLARE @sourceCount INT;
        --目标表现有记录数
        DECLARE @targetOriginCount INT;
        --目标表删除的记录数
        DECLARE @deleteCount INT;
        --目标表插入的记录数
        DECLARE @insertCount INT;
        --目标表更新的记录数
        DECLARE @updateCount INT;
        --任务开始时间
        DECLARE @startTime DATETIME;
        --目标表记录数
        DECLARE @targetCount INT;
        --动态Sql
        DECLARE @sql NVARCHAR(200);

        --记录任务开始时间
        SET @startTime = GETDATE();
        SELECT @sourceTableView=SourceTableView, @targetTable=TargetTable, @operation=Operation FROM ODS.DW_SP_INFO 
        WHERE ProcedureName=@procedureName AND IsDeleted = 0;
        --获取目标表现有的记录条数
        IF CHARINDEX(',',@targetTable) = 0
        BEGIN
            SET @sql = 'SELECT @targetOriginCount = COUNT(1) FROM ' + @targetTable
            EXEC sp_executesql @sql, N'@targetOriginCount INT OUTPUT', @targetOriginCount = @targetOriginCount OUTPUT
        END

        SET XACT_ABORT ON;
        BEGIN TRANSACTION myTran;
            EXEC @procedureName @sourceCount OUTPUT,@deleteCount OUTPUT, @insertCount OUTPUT, @updateCount OUTPUT
        
            --获取目标表现有的记录条数
            IF CHARINDEX(',',@targetTable) = 0
            BEGIN
                SET @sql = 'SELECT @targetCount = COUNT(1) FROM ' + @targetTable
                EXEC sp_executesql @sql, N'@targetCount INT OUTPUT', @targetCount = @targetCount OUTPUT
            END
        COMMIT TRANSACTION myTran;

        --任务完成，标记所有操作的过程记录
        INSERT INTO ODS.DW_SP_LOG (ProcedureName, StartTime, EndTime, Operation, SourceTableView, SourceCount, TargetTable, TargetOriginCount, DeleteCount, InsertCount, UpdateCount, TargetCount, RunResult)
        VALUES (@procedureName, @startTime, GETDATE(), @operation, @sourceTableView, @sourceCount, @targetTable, @targetOriginCount, @deleteCount, @insertCount, @updateCount, @targetCount, 'success');
    END TRY

    --错误捕获，记录错误信息
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION myTran

            INSERT INTO ODS.DW_SP_LOG (ProcedureName, StartTime, EndTime, Operation, SourceTableView, SourceCount, TargetTable, RunResult, ErrorNo, ErrorSeverity, ErrorState, ErrorLine, ErrorMessage)
            VALUES (@procedureName, @startTime, GETDATE(), @operation, @sourceTableView, @sourceCount, @targetTable, 'failure', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE());
        END
    END CATCH
END